﻿/*
    Copyright 2013 Bob Moss (bobbyjmoss@gmail.com)

   Licensed under the Apache License, Version 2.0 (the "License");
   you may not use this file except in compliance with the License.
   You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
 */

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace OpenRAMS.Model
{
    public class ClientRecord
    {
        //exposed attributes of client
        public String clientName { get; set; }
        public String clientOrg { get; set; }
        public String clientAddFirstLine { get; set; }
        public String clientAddSecondLine { get; set; }
        public String clientAddThirdLine { get; set; }
        public String clientAddPostCode { get; set; }
        public String clientPhoneLandLine { get; set; }
        public String clientPhoneMobile { get; set; }
        public String clientAddEmail { get; set; }
        public String clientJobTitle { get; set; }
        public String clientJobDept { get; set; }
        public String clientColour { get; set; }
        public DateTime createdTimeStamp { get; set; }

        //internal class variables/arrays
        protected String[] attribNames;
        protected int numAttribs;
        private bool NewOrOld;
        protected String tableName;

        //database-related variables
        private SqlConnection dbConnection;
        private SqlDataReader dbReader;
        private SqlCommand dbCommand;
        private String dbInString;

        public ClientRecord()
        {
            initHardCodedVals();
            NewOrOld = true;    //if true, is new record

            //Initialise with sensible defaults
            clientName = "";
            clientOrg = "";
            clientAddFirstLine = "";
            clientAddSecondLine = "";
            clientAddThirdLine = "";
            clientPhoneLandLine = "";
            clientPhoneMobile = "";
            clientAddEmail = "";
            clientJobTitle = "";
            clientJobDept = "";
            clientColour = "#FFFFFF";
            createdTimeStamp = Convert.ToDateTime(System.DateTime.Now.ToString());

            //reads in db connection string
            try { dbInString = ConfigurationManager.ConnectionStrings["default"].ConnectionString; }
            catch (Exception e) { throw e; } //this will cause error conditions later
        }

        public int Load(String cName)
        {
            String queryFull = "SELECT * FROM " + tableName + " ", queryCondition = "WHERE " + attribNames[0] + "='" + cName + "'"; //strings that'll handle query.
            int rCheck = 0; //used for error-checking later
            String[] outputVars = new String[numAttribs + 1];

            NewOrOld = false;   //importing in a new record

            //attempt DB connection
            try
            {
                dbConnection = new SqlConnection(dbInString);
                dbConnection.Open();
            }
            catch { return -1; }

            //verify input username is valid
            if (cName == "" || cName == "*" || cName == " ") { return -1; }

            //build query
            queryFull += queryCondition;
            dbCommand = dbConnection.CreateCommand();
            dbCommand.CommandText = queryFull;
            dbCommand.CommandType = CommandType.Text;
            dbCommand.CommandTimeout = 15;

            //Run command, and tidy-up if exceptions occur
            try { dbReader = dbCommand.ExecuteReader(); }
            catch
            {
                dbReader.Close();
                dbConnection.Close();
                return -1;
            }

            //initialise output array
            for (int i = 0; i < outputVars.Length; i++)
            {
                outputVars[i] = "";
            }

            //Import values from query into string array
            while (dbReader.Read())
            {
                if (rCheck > 0)
                {
                    return -1;  //if it's a primary key, only one result should be returned. Error if not the case
                }
                else
                {
                    int i = 0;  //iterator for output string
                    foreach (String att in attribNames)
                    {
                        try { outputVars[i] = dbReader[att].ToString(); }
                        catch { return -1; }
                        i++;
                    }
                }
                rCheck++;   //tracks number of results
            }

            //Verify that at least one result was returned
            if (rCheck < 1) { return -1; };

            //Import values from string array to class variables
            clientName = outputVars[0];
            clientOrg = outputVars[1];
            clientAddFirstLine = outputVars[2];
            clientAddSecondLine = outputVars[3];
            clientAddThirdLine = outputVars[4];
            clientAddPostCode = outputVars[5];
            clientPhoneLandLine = outputVars[6];
            clientPhoneMobile = outputVars[7];
            clientAddEmail = outputVars[8];
            clientJobTitle = outputVars[9]; 
            clientJobDept = outputVars[10];
            clientColour = outputVars[11];

            try { createdTimeStamp = DateTime.Parse(outputVars[12]); }
            catch { return -1; }

            //Clean-up DB connection
            try
            {
                dbReader.Close();
                dbConnection.Close();
            }
            catch { return -1; }

            NewOrOld = false; //fail-safe
            return 0;
        }

        public int Save()
        {
            //Strings to handle query as it's built
            String queryFull = "", queryCondition = "";

            //verify input username is valid
            if (clientName == "" || clientName == "*" || clientName == " ") { return -1; }

            //is this a new record? If so, we need an INSERT statement. Else, an UPDATE
            if (NewOrOld == true)
            {
                queryFull = "INSERT INTO " + tableName + " VALUES (";
                queryFull += "'" + clientName + "', ";
                queryFull += "'" + clientOrg + "', ";
                queryFull += "'" + clientAddFirstLine + "', ";
                queryFull += "'" + clientAddSecondLine + "', ";
                queryFull += "'" + clientAddThirdLine + "', ";
                queryFull += "'" + clientAddPostCode + "', ";
                queryFull += "'" + clientPhoneLandLine + "', ";
                queryFull += "'" + clientPhoneMobile + "', ";
                queryFull += "'" + clientAddEmail + "', ";
                queryFull += "'" + clientJobTitle + "', ";
                queryFull += "'" + clientJobDept + "', ";
                queryFull += "'" + clientColour + "', ";
                queryFull += "'" + createdTimeStamp.ToString("dd-MMMM-yyyy H:mm:ss") + "')";
            }
            else
            {
                //verify the record actually exists
                ClientRecord test = new ClientRecord();
                int initCheck = test.Load(clientName);
                if (initCheck < 0) { return -1; }

                //build the UPDATE query (clientName is skipped because it's a primary key)
                queryFull = "UPDATE " + tableName + " SET ";
                queryCondition = "WHERE " + attribNames[0] + "='" + clientName + "'";
                queryFull += attribNames[1] + "='" + clientOrg + "', ";
                queryFull += attribNames[2] + "='" + clientAddFirstLine + "', ";
                queryFull += attribNames[3] + "='" + clientAddSecondLine + "', ";
                queryFull += attribNames[4] + "='" + clientAddThirdLine + "', ";
                queryFull += attribNames[5] + "='" + clientAddPostCode + "', ";
                queryFull += attribNames[6] + "='" + clientPhoneLandLine + "', ";
                queryFull += attribNames[7] + "='" + clientPhoneMobile + "', ";
                queryFull += attribNames[8] + "='" + clientAddEmail + "', ";
                queryFull += attribNames[9] + "='" + clientJobTitle + "', ";
                queryFull += attribNames[10] + "='" + clientJobDept + "', ";
                queryFull += attribNames[11] + "='" + clientColour + "', ";
                queryFull += attribNames[12] + "='" + createdTimeStamp.ToString("dd-MMMM-yyyy H:mm:ss") + "' ";
                queryFull += queryCondition;
            }

            //attempt DB connection
            try
            {
                dbConnection = new SqlConnection(dbInString);
                dbConnection.Open();
            }
            catch { return -1; }

            //create command that will run query
            dbCommand = dbConnection.CreateCommand();
            dbCommand.CommandText = queryFull;
            dbCommand.CommandType = CommandType.Text;
            dbCommand.CommandTimeout = 15;

            //Run command, and tidy-up if exceptions occur
            try { dbReader = dbCommand.ExecuteReader(); }
            catch
            {
                dbReader.Close();
                dbConnection.Close();
                return -1;
            }

            //Clean-up DB connection
            try
            {
                dbReader.Close();
                dbConnection.Close();
            }
            catch { return -1; }

            //check if saved correctly if new
            if (NewOrOld == true)
            {
                int saveCheck = Load(clientName);
                if (saveCheck < 0) { return -1; }
            }

            NewOrOld = false;   //fail-safe
            return 0;
        }

        public int Delete()
        {
            //Use with caution! This will fail if orphaned entries exist
            String queryFull = "DELETE FROM " + tableName, queryCondition = " WHERE " + attribNames[0] + "='" + clientName + "'";

            //verify input username is valid
            if (clientName == "" || clientName == "*" || clientName == " ") { return -1; }

            //Check record exists in the first place
            ClientRecord test = new ClientRecord();
            int initCheck = test.Load(clientName);
            if (initCheck < 0) { return -1; }

            /* TO DO - UNCOMMENT AND TEST LINES BELOW */
            // block delete if there are any bookings the client is associated with
            //Booking bookingNew = new Booking();
            //int bookClientCheck = bookingNew.Search(2, clientName);    //uses attribID & string as parameters
            //if(bookClientCheck > -1) { return -1; }

            queryFull += queryCondition; //pull query together

            //attempt DB connection
            try
            {
                dbConnection = new SqlConnection(dbInString);
                dbConnection.Open();
            }
            catch { return -1; }

            //create command that will run query
            dbCommand = dbConnection.CreateCommand();
            dbCommand.CommandText = queryFull;
            dbCommand.CommandType = CommandType.Text;
            dbCommand.CommandTimeout = 15;

            //Run command, and tidy-up if exceptions occur
            try { dbReader = dbCommand.ExecuteReader(); }
            catch
            {
                dbReader.Close();
                dbConnection.Close();
                return -1;
            }

            //Check was deleted successfully
            int delCheck = Load(clientName);
            if (delCheck > -1) { return -1; }

            NewOrOld = true;    //as the record no longer exists in the database, is considered 'new'

            //Clean-up DB connection
            try
            {
                dbReader.Close();
                dbConnection.Close();
            }
            catch { return -1; }

            return 0;
        }

        protected void initHardCodedVals()
        {
            //set hard-coded values
            numAttribs = 12;
            tableName = "tblClients";
            attribNames = new String[numAttribs + 1];
            attribNames[0] = "txtClientName"; //primary key value
            attribNames[1] = "txtOrganisation";
            attribNames[2] = "txtAddressFirstLine";
            attribNames[3] = "txtAddressSecondLine";
            attribNames[4] = "txtAddressThirdLine";
            attribNames[5] = "txtAddressPostCode";
            attribNames[6] = "txtLandLinePhoneNo";
            attribNames[7] = "txtMobilePhoneNo";
            attribNames[8] = "txtEmailAddress";
            attribNames[9] = "txtJobTitle";
            attribNames[10] = "txtDepartment";
            attribNames[11] = "txtColour";
            attribNames[12] = "dtTimeStamp";
        }
    }
}